﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Text;

public partial class SSFGL_SSFGLKHList : System.Web.UI.Page
{
  protected void Page_Load(object sender, EventArgs e)
  {
    if (!Page.IsPostBack)
    {
      if (!ProjectPrivate.GetQX("10"))
      {
        Server.Transfer("~\\blank.aspx");
      }
      if (CWSD.GetSDZT("否"))
      {
        Server.Transfer("~\\sd.aspx");
      }
    }
    BindGrid();
  }

  protected void BindGrid()
  {
    StringBuilder sbSql = new StringBuilder();
    sbSql.Append("SELECT  ROW_NUMBER() OVER (ORDER BY a.khid desc) AS xh,a.*,c.*,n.* FROM ");
    sbSql.Append(@"(select a.khid,(isnull(a.ysfy,0)-isnull(b.yjfy,0)) SSFY from 
(select b.khid,isnull(sum(ysfy),0) ysfy from sjlr_bqql a,sjlr_cbsj b 
WHERE a.cbsjid=b.cbsjid and a.bqql_sfsb=2 group by b.khid) a left join 
(select khid,isnull(sum(bqsj),0) yjfy from sjlr_jfsj a group by a.khid) b
  on a.khid=b.khid
  where (isnull(a.ysfy,0)-isnull(b.yjfy,0))>0) c ");
    sbSql.Append(@" join (select a.*,b.KHJB,c.DWMC from jcxx_khxx a,jcxx_khjb b,jcxx_dw c where a.khjbid=b.khjbid and a.dwid=c.dwid) A
on c.khid=a.khid ");
    sbSql.Append(@"
OUTER APPLY(
    SELECT 
        YQXZ=STUFF(
      REPLACE(
       REPLACE((select yqxz from 
   (SELECT yqxz,khid FROM jcxx_yqxz b,jcxx_khyqxz N WHERE b.yqxzid=n.yqxzid ) b
    WHERE b.khid = c.khid FOR XML AUTO), ");
    sbSql.Append("'<b yqxz=\"', ','),'\"/>', ''), 1, 1, '')) N ");
    DataSet ds = DbHelperSQL.Query(sbSql.ToString());
    ASPxGridView1.DataSource = ds;
    ASPxGridView1.DataBind();
  }

  protected void lbDelete_Command(object sender, CommandEventArgs e)
  {
    SqlParameter[] cmdParams = null;
    string sSql = "";
    List<CommandInfo> cis = new List<CommandInfo>();
    CommandInfo ci;

    sSql = "delete from SJLR_yjlr where yjid=" + e.CommandArgument.ToString();
    ci = new CommandInfo();
    ci.CommandText = sSql;
    ci.Parameters = cmdParams;
    cis.Add(ci);

    if (DbHelperSQL.ExecuteSqlTran(cis) > 0)
    {
      BindGrid();
    }
  }

  protected void btnQuery_Click(object sender, EventArgs e)
  {

    //    StringBuilder sbSql = new StringBuilder();
    //    sbSql.Append("SELECT c.SSFY,a.*,n.* FROM");
    //    sbSql.Append(@"(select a.khid,sum(ssfy*-1) SSFY from sjlr_cbsj a,sjlr_bqql b where a.cbsjid=b.cbsjid and ssfy<0 and bqql_sfsb=2 group by a.khid) c right join ");
    //    sbSql.Append(" (select a.*,b.KHJB,c.DWMC from jcxx_khxx a,jcxx_khjb b,jcxx_dw c where a.khjbid=b.khjbid and a.dwid=c.dwid ");
    //    if (!string.IsNullOrEmpty(txtKHBM.Text))
    //    {
    //      sbSql.Append("and a.khid='" + txtKHBM.Text + "' ");
    //    }
    //    if (!string.IsNullOrEmpty(txtKHMC.Text))
    //    {
    //      sbSql.Append("and a.khmc like '%" + txtKHMC.Text + "%' ");
    //    }
    //    if (!string.IsNullOrEmpty(txtBS.Text))
    //    {
    //      sbSql.Append("and a.kh like '%" + txtBS.Text + "%' ");
    //    }
    //    sbSql.Append(@"
    //)A on c.khid=a.khid 
    //OUTER APPLY(
    //    SELECT 
    //        YQXZ=STUFF(
    //      REPLACE(
    //       REPLACE((select yqxz from 
    //   (SELECT yqxz,khid FROM jcxx_yqxz b,jcxx_khyqxz N WHERE b.yqxzid=n.yqxzid ) b
    //    WHERE b.khid = A.khid FOR XML AUTO), ");
    //    sbSql.Append("'<b yqxz=\"', ','),'\"/>', ''), 1, 1, '')) N order by a.khid desc");
    //    SqlParameter[] cmdParams = { new SqlParameter("@2", SqlDbType.VarChar, 20), new SqlParameter("@3", SqlDbType.VarChar, 20) };
    //    cmdParams[0].Value = txtKHBM.Text;
    //    cmdParams[1].Value = txtKHMC.Text;
    //    DataSet ds = DbHelperSQL.Query(sbSql.ToString());
    //    GridViewManage.AddXH(ds);
    //    ASPxGridView1.DataSource = ds;
    //    ASPxGridView1.DataBind();
  }
}
